package com.pinusmusic.dao.impl;

import com.pinusmusic.dao.MusicDao;
import com.pinusmusic.domain.*;
import com.pinusmusic.util.JDBCUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class MusicDaoImpl implements MusicDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<Music> findMusicsByName(int start, int rows, String name) {
        // m.musicname like ? or s.singername like ?
        //如果用户不输入信息则不显示
        if (name !=null && !name.trim().isEmpty() && !name.equals("")){
            String sql = "select distinct m.* from music m where 1=0"; //用于拼接where子句条件
            StringBuilder sb = new StringBuilder(sql);
            ArrayList<Object> params = new ArrayList<>();//存放musicName的值
            sb.append(" or m.musicname like ?");
            params.add("%"+name+"%");
            //分页
            sb.append("limit ?,?");
            params.add(start);
            params.add(rows);
            List<Music> musicList = template.query(sb.toString(), new BeanPropertyRowMapper<>(Music.class), params.toArray());
            return musicList.size()==0?null:musicList;
        }
        return null;
    }

    @Override
    public int getTotalCount(String name) {
        String sql = "select count(*) from music m where 1=0";
        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();//存放musicName的值
        if (name !=null && !name.trim().isEmpty()){
            sb.append(" or m.musicname like ?");
            params.add("%"+name+"%");
        }
        return template.queryForObject(sb.toString(),Integer.class,params.toArray());
    }
    @Override
    public void deleteMusicBySid(int sid) {
        String sql = "delete from music where sid=?";
        template.update(sql,sid);
    }
    @Override
    public List<Music> findMusicBySid(int sid) {
        String sql = "select * from music where sid=?";
        List<Music> musicList = template.query(sql, new BeanPropertyRowMapper<>(Music.class), sid);
        return musicList.size()==0?null:musicList;
    }

    @Override
    public int getTotalCount(int mlid) {
        String sql = "select count(*) from  music where mid not in" +
                "(select m.mid from music m inner join music_musiclist mm on m.mid = mm.mid where mm.mlid =?)";
        return template.queryForObject(sql,Integer.class,mlid);
    }

    @Override
    public List<Music> findAllMusicByPage(int start, int rows, int mlid) {
        String sql = "select * from music where mid not in" +
                "(select m.mid from music m inner join music_musiclist mm on m.mid = mm.mid where mm.mlid =?) " +
                "limit ?,?";
        return template.query(sql,new BeanPropertyRowMapper<>(Music.class),mlid,start,rows);
    }

    @Override
    public List<Music> findMusicInListByPage(int start, int rows, int mlid) {
        String sql = "select * from music where mid in" +
                "(select m.mid from music m inner join music_musiclist mm on m.mid = mm.mid where mm.mlid =?) " +
                "limit ?,?";
        return template.query(sql,new BeanPropertyRowMapper<>(Music.class),mlid,start,rows);
    }

    @Override
    public int getCount(int mlid) {
        String sql = "select count(*) from  music where mid in" +
                "(select m.mid from music m inner join music_musiclist mm on m.mid = mm.mid where mm.mlid =?)";
        return template.queryForObject(sql,Integer.class,mlid);
    }

    @Override
    public boolean musicUpload(Music music) {
        boolean flag=false;
        String sql="insert into music values (null,?,?,?)";
        if (template.update(sql,music.getMusicname(),music.getMusicpath(),music.getSid())!=0){
            flag=true;
        }
        return flag;
    }

    @Override
    public int findSidBySingername(String singername) {
        String sql="select sid from singer where singername=?";
        int sid;
        try {
            sid=template.queryForObject(sql,Integer.class,singername);
            System.out.println("歌手"+singername+"id为"+sid);
        }catch (EmptyResultDataAccessException e){
            sid=-1;//如果查询结果为空或多个，则返回-1
        }

        return sid;
    }

    @Override
    public boolean musicDelete(int mid) {
        boolean flag=false;
        String sql="delete from music where mid=?";
        if (template.update(sql,mid)!=0){
            System.out.println("删除执行完成");
            flag=true;
        }
        return flag;
    }

    @Override
    public String findPathByMid(int mid) {
        String sql="select musicpath from music where mid=?";
        String musicPath=null;
        try {
            musicPath="src\\main\\webapp\\"+template.queryForObject(sql,String.class,mid);
            System.out.println("删除使用的文件路径为"+musicPath);
        }catch (EmptyResultDataAccessException e){
            System.out.println("路径查询失败");//如果查询结果为空或多个，则返回-1
        }
        return musicPath;
    }

    @Override
    public boolean musicUpdate(Music music) {
        boolean flag=false;
        String sql=" update music set musicname=?,musicpath=?,sid=? where mid=?";
        if (template.update(sql,music.getMusicname(),music.getMusicpath(),music.getSid(),music.getMid())!=0){
            flag=true;
        }
        return flag;
    }

    @Override
    public int getTotalCountBySid(Integer sid) {
        String sql = "select count(*) from music where sid=?";
        return template.queryForObject(sql,Integer.class,sid);
    }

    @Override
    public List<Music> findSingerMusicByPage(int start, int rows, Integer sid) {
        String sql = "select * from  music where sid=? limit ?,?";
        return template.query(sql,new BeanPropertyRowMapper<>(Music.class),sid,start,rows);
    }

    @Override
    public Music findMusicByMid(int mid) {
        String sql = "select * from music where mid=?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<>(Music.class),mid);
    }

    @Override
    public List<Music> find20Music() {
        String sql="select * from music limit 6 offset 0";
        List<Music> list = template.query(sql, new BeanPropertyRowMapper<>(Music.class));
        return list;
    }

    @Override
    public List<Music> findMusicsByLyrics(int start, int rows, String lyrics) {
        if (lyrics !=null && !lyrics.trim().isEmpty() && !lyrics.equals("")){
            String sql = "select distinct m.* from music m where 1=0"; //用于拼接where子句条件
            StringBuilder sb = new StringBuilder(sql);
            ArrayList<Object> params = new ArrayList<>();//存放musicName的值
            sb.append(" or m.lyrics like ?");
            params.add("%"+lyrics+"%");
            //分页
            sb.append("limit ?,?");
            params.add(start);
            params.add(rows);
            List<Music> musicList = template.query(sb.toString(), new BeanPropertyRowMapper<>(Music.class), params.toArray());
            return musicList.size()==0?null:musicList;
        }
        return null;
    }

    @Override
    public int getCountByLyrics(String lyrics) {
        String sql = "select count(*) from music  where 1=0";
        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();
        if (lyrics !=null && !lyrics.trim().isEmpty()){
            sb.append(" or lyrics like ?");
            params.add("%"+lyrics+"%");
        }
        return template.queryForObject(sb.toString(),Integer.class,params.toArray());
    }

    @Override
    public List<Music> findMusicsBySort(int start, int rows, int languageid, int styleid) {
        String sql = "select * from music where 1=1";
        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();
        if(languageid!=0){
            sb.append(" and musiclanguageid = ?");
            params.add(languageid);
        }
        if (styleid!=0){
            sb.append(" and musicstyleid = ? ");
            params.add(styleid);
        }
        sb.append(" limit ?,?");
        params.add(start);
        params.add(rows);
        List<Music> musicList = template.query(sb.toString(), new BeanPropertyRowMapper<>(Music.class), params.toArray());
        return musicList.size()!=0?musicList:null;
    }

    @Override
    public int getCountBySort(int languageid, int styleid) {
        String sql = "select count(*) from music  where 1=1";
        StringBuilder sb = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();
        if(languageid!=0){
            sb.append(" and musiclanguageid = ?");
            params.add(languageid);
        }
        if (styleid!=0){
            sb.append(" and musicstyleid = ?");
            params.add(styleid);
        }
        return template.queryForObject(sb.toString(),Integer.class,params.toArray());
    }

    @Override
    public MusicStyle findMusicStyle(Integer musicstyleid) {
        String sql = "select * from musicstyle where musicstyleid = ?";
        List<MusicStyle> list = template.query(sql, new BeanPropertyRowMapper<>(MusicStyle.class), musicstyleid);
        return list.get(0);
    }

    @Override
    public MusicLanguage findMusicLang(Integer musiclanguageid) {
        String sql = "select * from musiclanguage where musiclanguageid = ?";
        List<MusicLanguage> list = template.query(sql, new BeanPropertyRowMapper<>(MusicLanguage.class), musiclanguageid);
        return list.get(0);
    }

    @Override
    public List<Comment> findCommentsByMid(Integer mid) {
        String sql = "select * from comment where mid = ?";
        List<Comment> query = template.query(sql, new BeanPropertyRowMapper<>(Comment.class), mid);
        return query;
    }

    @Override
    public User findUserByUid(Integer uid) {
        String sql = "select * from user where uid = ?";
        List<User> query = template.query(sql, new BeanPropertyRowMapper<>(User.class), uid);
        return query.get(0);
    }

    @Override
    public int findCommentsCounts(Integer mid) {
        String sql = "select count(*) from comment where mid = ?";
        Integer counts = template.queryForObject(sql, Integer.class, mid);
        return counts;
    }

    @Override
    public void addComment(Comment comment) {
        String sql = "insert into comment values(null,?,?,?,?)";
        template.update(sql,comment.getMid(),comment.getCommenttext(),comment.getUid(),comment.getData());
    }

    @Override
    public Singer findSingerByMid(Integer mid) {
        String sql = "select * from singer where sid = ?";
        List<Singer> list = template.query(sql, new BeanPropertyRowMapper<>(Singer.class),mid);
        return list.get(0);
    }

    @Override
    public List<Music> findAllMusic() {
        String sql = "select * from music";
        List<Music> query = template.query(sql, new BeanPropertyRowMapper<>(Music.class));
        return query;
    }

    @Override
    public Singer findSingerBySid(Integer sid) {
        String sql = "select * from singer where sid = ?";
        List<Singer> query = template.query(sql, new BeanPropertyRowMapper<>(Singer.class), sid);
        return query.get(0);
    }

    @Override
    public int findFavorsCounts(Integer mid) {
        String sql = "select count(*) from favorite where mid = ?";
        Integer counts = template.queryForObject(sql, Integer.class, mid);
        return counts;
    }

    @Override
    public List<Music> findMusicsByMlid(Integer mlid) {
        String sql = "select * from music where mid in " +
                "(select mid from music_musiclist where mlid=?)";
        return template.query(sql,new BeanPropertyRowMapper<>(Music.class),mlid);
    }

}
